﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entidades;

namespace AccesoBD
{
    public class Especialidades
    {
        public static void Insertar(Especialidad esp, out int filasAfectadas)
        {
            filasAfectadas = 0;
            SqlConnection conexion = DB.Conectar();
            SqlTransaction trans = null;
            try
            {
                trans = conexion.BeginTransaction();

                //1- Insertamos la modalidad
                string comando = "INSERT INTO Modalidad_Atencion(duracionAtencion, fechaVigenciaDesde, fechaVigenciaHasta, idTipoModalidad) VALUES(@duracion,@fechaDesde,@fechaHasta,@idTipoMod)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@duracion", esp.ModalidadEspecialidad.DuracionAtencion));
                sqlcmd.Parameters.Add(new SqlParameter("@fechaDesde", esp.ModalidadEspecialidad.FechaVigenciaDesde));
                if (esp.ModalidadEspecialidad.FechaVigenciaHasta == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", esp.ModalidadEspecialidad.FechaVigenciaHasta));
                sqlcmd.Parameters.Add(new SqlParameter("@idTipoMod", esp.ModalidadEspecialidad.CodTipoModalidad));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                //2- obtenemos el ID de la modalidad
                comando = "SELECT @@identity FROM Modalidad_Atencion";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Transaction = trans;
                int IDmodAt = int.Parse(sqlcmd.ExecuteScalar().ToString());
                esp.CodModalidad = IDmodAt;

                //3- insertamos la especialidad
                comando = "INSERT INTO Especialidad(nombre, descripcion, fechaAlta, precioLista, precioMedico, modalidad) VALUES(@nombre,@descripcion,@fechaAlta,@precioL,@precioM,@modAtencion)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombre", esp.Nombre));
                if (esp.Descripcion == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@descripcion", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@descripcion", esp.Descripcion));
                sqlcmd.Parameters.Add(new SqlParameter("@fechaAlta", esp.FechaAlta));
                sqlcmd.Parameters.Add(new SqlParameter("@precioL", esp.PrecioLista));
                sqlcmd.Parameters.Add(new SqlParameter("@precioM", esp.PrecioMedico));
                sqlcmd.Parameters.Add(new SqlParameter("@modAtencion", esp.CodModalidad));
                sqlcmd.Transaction = trans;
                filasAfectadas = sqlcmd.ExecuteNonQuery();

                if (filasAfectadas > 0)
                    trans.Commit();
            }
            catch (Exception)
            {
                if (trans != null)
                    trans.Rollback();
                throw;
            }
            finally
            {
                conexion.Close();
            }
        }

        public static List<Especialidad> ObtenerEspecialidades()
        {
            List<Especialidad> LE = new List<Especialidad>();
            Especialidad objEsp = null;
            ModalidadAtencion objModAt = null;
            TipoModalidad objTipoMod = null;
            SqlConnection con = null;
            try
            {
                con = DB.Conectar();
                string comando = "SELECT Especialidad.codEspecialidad, Especialidad.nombre, Especialidad.descripcion, Especialidad.fechaAlta, Especialidad.precioLista, "
                     + "Modalidad_Atencion.codModalidad AS 'CodModalidad', Modalidad_Atencion.duracionAtencion, Modalidad_Atencion.fechaVigenciaDesde, "
                     + "Tipo_Modalidad.codTipoModalidad AS 'CodTipoModalidad', Tipo_Modalidad.nombre AS 'NombreTipoModalidad' FROM Especialidad INNER JOIN "
                     + "Modalidad_Atencion ON Especialidad.modalidad = Modalidad_Atencion.codModalidad INNER JOIN "
                     + "Tipo_Modalidad ON Modalidad_Atencion.idTipoModalidad = Tipo_Modalidad.codTipoModalidad "
                     + "WHERE Especialidad.fechaBaja IS NULL  and (Modalidad_Atencion.fechaVigenciaHasta > GETDATE() or Modalidad_Atencion.fechaVigenciaHasta is null)";
                SqlCommand sqlcmd = new SqlCommand(comando, con);
                SqlDataReader dr = sqlcmd.ExecuteReader();
                while (dr.Read())
                {
                    //solo mostramos las especialidades "vigentes"
                    //o sea, las que NO tienen fecha de baja dada, NI modalidad de atencion vigente

                    //if (dr["fechaBaja"] == DBNull.Value)
                    //{
                    objEsp = new Especialidad();
                    objEsp.CodEspecialidad = (int)dr["codEspecialidad"];
                    objEsp.Nombre = dr["nombre"].ToString();
                    if (dr["descripcion"] != DBNull.Value)
                        objEsp.Descripcion = dr["descripcion"].ToString();
                    objEsp.FechaAlta = Convert.ToDateTime(dr["fechaAlta"]);

                    objEsp.PrecioLista = float.Parse(dr["precioLista"].ToString());
                    objEsp.CodModalidad = (int)dr["CodModalidad"];

                    objModAt = new ModalidadAtencion();
                    objModAt.CodModalidad = (int)dr["CodModalidad"];
                    objModAt.DuracionAtencion = (int)dr["duracionAtencion"];
                    objModAt.FechaVigenciaDesde = Convert.ToDateTime(dr["fechaVigenciaDesde"]);
                    objModAt.CodTipoModalidad = (int)dr["CodTipoModalidad"];

                    objTipoMod = new TipoModalidad();
                    objTipoMod.CodTipoModalidad = (int)dr["CodTipoModalidad"];
                    objTipoMod.Nombre = dr["NombreTipoModalidad"].ToString();

                    objModAt.TipoModalidad = objTipoMod;
                    objEsp.ModalidadEspecialidad = objModAt;

                    LE.Add(objEsp);
                    objEsp = null;
                    //}
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error al cargar Especialidades", ex);
            }
            finally { con.Close(); }
            return LE;
        }

        /// <summary>
        /// Obtiene las Especialidades vigentes que atienden por turnos.
        /// </summary>
        /// <param name="nombre"></param>
        /// <returns></returns>
        public static List<Especialidad> ObtenerEspecialidadesPorTurno()
        {
            List<Especialidad> listaEspecialidades = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT Especialidad.codEspecialidad, Especialidad.nombre, Tipo_Modalidad.nombre AS tipoModalidad, Tipo_Modalidad.codTipoModalidad "
                               + "FROM Especialidad INNER JOIN Modalidad_Atencion ON Especialidad.modalidad = Modalidad_Atencion.codModalidad INNER JOIN "
                               + "Tipo_Modalidad ON Modalidad_Atencion.idTipoModalidad = Tipo_Modalidad.codTipoModalidad "
                               + "WHERE (Tipo_Modalidad.codTipoModalidad = 1) AND (Especialidad.fechaBaja IS NULL OR Especialidad.fechaBaja > GETDATE())";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                SqlDataReader dr = sqlcmd.ExecuteReader();

                if (dr.HasRows)
                {
                    listaEspecialidades = new List<Especialidad>();
                    Especialidad objEsp = null;
                    while (dr.Read())
                    {
                        objEsp = new Especialidad();
                        objEsp.CodEspecialidad = int.Parse(dr["codEspecialidad"].ToString());
                        objEsp.Nombre = dr["nombre"].ToString();

                        listaEspecialidades.Add(objEsp);
                        objEsp = null;
                    }
                }

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return listaEspecialidades;
        }

        /// <summary>
        /// Obtiene las Especialidades vigentes que atienden por orden de llegada.
        /// </summary>
        /// <param name="nombre"></param>
        /// <returns></returns>
        public static List<Especialidad> ObtenerEspecialidadesPorOrdenLlegada()
        {
            List<Especialidad> listaEspecialidades = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT Especialidad.codEspecialidad, Especialidad.nombre, Tipo_Modalidad.nombre AS tipoModalidad, Tipo_Modalidad.codTipoModalidad "
                               + "FROM Especialidad INNER JOIN Modalidad_Atencion ON Especialidad.modalidad = Modalidad_Atencion.codModalidad INNER JOIN "
                               + "Tipo_Modalidad ON Modalidad_Atencion.idTipoModalidad = Tipo_Modalidad.codTipoModalidad "
                               + "WHERE (Tipo_Modalidad.codTipoModalidad = 2) AND (Especialidad.fechaBaja IS NULL OR Especialidad.fechaBaja > GETDATE())";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                SqlDataReader dr = sqlcmd.ExecuteReader();

                if (dr.HasRows)
                {
                    listaEspecialidades = new List<Especialidad>();
                    Especialidad objEsp = null;
                    while (dr.Read())
                    {
                        objEsp = new Especialidad();
                        objEsp.CodEspecialidad = int.Parse(dr["codEspecialidad"].ToString());
                        objEsp.Nombre = dr["nombre"].ToString();

                        listaEspecialidades.Add(objEsp);
                        objEsp = null;
                    }
                }

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return listaEspecialidades;
        }

        public static Especialidad ObtenerEspecialidadPorNombre(string nombre)
        {
            Especialidad oE = new Especialidad();
            SqlConnection con = null;

            try
            {
                con = DB.Conectar();
                string comando = "select * as Codigo from Especialidad where nombre=@nombre ";
                List<SqlParameter> lisp = new List<SqlParameter>();
                lisp.Add(new SqlParameter("@nombre", nombre));
                SqlDataReader dr = DB.GenerarReader(comando, lisp, con);
                if (dr.Read())
                {
                    oE.CodEspecialidad = (int)dr["codEspecialidad"];
                    oE.Nombre = dr["nombre"].ToString();
                    oE.Descripcion = dr["descripcion"].ToString();
                    oE.FechaAlta = Convert.ToDateTime(dr["fechaAlta"]);
                    oE.FechaBaja = Convert.ToDateTime(dr["fechaBaja"]);
                    oE.PrecioLista = (float)dr["precioLista"];
                    oE.PrecioMedico = (float)dr["precioMedico"];
                    oE.CodModalidad = (int)dr["modalidad"];
                }
                return oE;
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar especialidad por nombre", ex);
            }
            finally
            {
                con.Close();
            }
        }

        public static Especialidad ObtenerEspecialidad(int codigo)
        {
            Especialidad esp = null;
            ModalidadAtencion objModAt = null;
            TipoModalidad objTipoMod = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT Especialidad.codEspecialidad, Especialidad.nombre, Especialidad.descripcion, Especialidad.fechaAlta, Especialidad.precioLista, Especialidad.precioMedico, "
                + "Modalidad_Atencion.codModalidad AS 'CodModalidad', Modalidad_Atencion.duracionAtencion, Modalidad_Atencion.fechaVigenciaDesde, "
                + "Tipo_Modalidad.codTipoModalidad AS 'CodTipoModalidad', Tipo_Modalidad.nombre AS 'NombreTipoModalidad' "
                + "FROM Especialidad INNER JOIN Modalidad_Atencion ON Especialidad.modalidad = Modalidad_Atencion.codModalidad "
                + "INNER JOIN Tipo_Modalidad ON Modalidad_Atencion.idTipoModalidad = Tipo_Modalidad.codTipoModalidad "
                + "WHERE Especialidad.codEspecialidad = @codEspecialidad";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@codEspecialidad", codigo));
                SqlDataReader dr = sqlcmd.ExecuteReader();
                while (dr.Read())
                {
                    esp = new Especialidad();
                    esp.CodEspecialidad = (int)dr["codEspecialidad"];
                    esp.Nombre = dr["nombre"].ToString();
                    if (dr["descripcion"] != DBNull.Value)
                        esp.Descripcion = dr["descripcion"].ToString();
                    esp.FechaAlta = Convert.ToDateTime(dr["fechaAlta"]);

                    esp.PrecioLista = float.Parse(dr["precioLista"].ToString());
                    esp.PrecioMedico = float.Parse(dr["precioMedico"].ToString());
                    esp.CodModalidad = (int)dr["CodModalidad"];

                    objModAt = new ModalidadAtencion();
                    objModAt.CodModalidad = (int)dr["CodModalidad"];
                    objModAt.DuracionAtencion = (int)dr["duracionAtencion"];
                    objModAt.FechaVigenciaDesde = Convert.ToDateTime(dr["fechaVigenciaDesde"]);
                    objModAt.CodTipoModalidad = (int)dr["CodTipoModalidad"];

                    objTipoMod = new TipoModalidad();
                    objTipoMod.CodTipoModalidad = (int)dr["CodTipoModalidad"];
                    objTipoMod.Nombre = dr["NombreTipoModalidad"].ToString();

                    objModAt.TipoModalidad = objTipoMod;
                    esp.ModalidadEspecialidad = objModAt;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return esp;
        }

        public static bool RegistrarBajaEspecialidad(int codigo, DateTime fechaBaja)
        {
            bool flag;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "UPDATE Especialidad SET fechaBaja = @fechaBaja WHERE (codEspecialidad = @codigo)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@codigo", codigo));
                sqlcmd.Parameters.Add(new SqlParameter("@fechaBaja", fechaBaja));
                int res = sqlcmd.ExecuteNonQuery();

                if (res > 0)
                    flag = true;
                else
                    flag = false;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return flag;
        }

        // modifica la modalidad de atencion
        public static bool ModificarEspecialidad(Especialidad espModif, DateTime fechaVigenciaModAnterior)
        {
            bool res;
            SqlConnection conexion = DB.Conectar();
            SqlTransaction trans = null;
            try
            {
                trans = conexion.BeginTransaction();

                //actualizamos la modalidad anterior, dandole de baja para luego crear la nueva modalidad con los cambios
                string comando = "UPDATE Modalidad_Atencion SET fechaVigenciaHasta = @fechaHasta WHERE (codModalidad = @idModAt)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", fechaVigenciaModAnterior));
                sqlcmd.Parameters.Add(new SqlParameter("@idModAt", espModif.CodModalidad));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                //ahora creamos la nueva modalidad para asignarselo a la especialidad
                comando = "INSERT INTO Modalidad_Atencion(duracionAtencion, fechaVigenciaDesde, idTipoModalidad, fechaVigenciaHasta) VALUES (@duracion,@fechaDesde,@idTipoMod,@fechaHasta)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@duracion", espModif.ModalidadEspecialidad.DuracionAtencion));
                sqlcmd.Parameters.Add(new SqlParameter("@fechaDesde", espModif.ModalidadEspecialidad.FechaVigenciaDesde));
                sqlcmd.Parameters.Add(new SqlParameter("@idTipoMod", espModif.ModalidadEspecialidad.CodTipoModalidad));
                if (espModif.ModalidadEspecialidad.FechaVigenciaHasta == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaHasta", espModif.ModalidadEspecialidad.FechaVigenciaHasta));
                sqlcmd.Transaction = trans;
                sqlcmd.ExecuteNonQuery();

                //obtenemos el nuevo id de la modalidad
                comando = "SELECT @@identity FROM Modalidad_Atencion";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Transaction = trans;
                int idNvaModalidad = int.Parse(sqlcmd.ExecuteScalar().ToString());
                espModif.CodModalidad = idNvaModalidad;

                //actualizamos la Especialidad con su nueva modalidad
                comando = "UPDATE Especialidad SET nombre = @nombre, descripcion = @descripcion, precioLista = @precioL, precioMedico = @precioM, modalidad = @idModalidad WHERE (codEspecialidad = @codEspecialidad)";
                sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombre", espModif.Nombre));
                if (espModif.Descripcion == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@descripcion", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@descripcion", espModif.Descripcion));
                sqlcmd.Parameters.Add(new SqlParameter("@precioL", espModif.PrecioLista));
                sqlcmd.Parameters.Add(new SqlParameter("@precioM", espModif.PrecioMedico));
                sqlcmd.Parameters.Add(new SqlParameter("@idModalidad", espModif.CodModalidad));
                sqlcmd.Parameters.Add(new SqlParameter("@codEspecialidad", espModif.CodEspecialidad));
                sqlcmd.Transaction = trans;
                int filas = sqlcmd.ExecuteNonQuery();

                trans.Commit();

                if (filas > 0)
                    res = true;
                else
                    res = false;
            }
            catch (Exception)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                conexion.Close();
            }
            return res;
        }

        // no modifica la modalidad de atencion
        public static bool ModificarEspecialidad(Especialidad espModif)
        {
            bool res;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "UPDATE Especialidad SET nombre = @nombre, descripcion = @descripcion, precioLista = @precioL, precioMedico = @precioM, modalidad = @idModalidad WHERE (codEspecialidad = @codEspecialidad)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@nombre", espModif.Nombre));
                if (espModif.Descripcion == null)
                    sqlcmd.Parameters.Add(new SqlParameter("@descripcion", DBNull.Value));
                else
                    sqlcmd.Parameters.Add(new SqlParameter("@descripcion", espModif.Descripcion));
                sqlcmd.Parameters.Add(new SqlParameter("@precioL", espModif.PrecioLista));
                sqlcmd.Parameters.Add(new SqlParameter("@precioM", espModif.PrecioMedico));
                sqlcmd.Parameters.Add(new SqlParameter("@idModalidad", espModif.CodModalidad));
                sqlcmd.Parameters.Add(new SqlParameter("@codEspecialidad", espModif.CodEspecialidad));
                int filas = sqlcmd.ExecuteNonQuery();

                if (filas > 0)
                    res = true;
                else
                    res = false;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conexion.Close();
            }
            return res;
        }

        public static List<Especialidad> ObtenerEspecialidades(string nombreEsp, DateTime? fechaDesde)
        {
            List<Especialidad> list = null;
            Especialidad objEsp = null;
            ModalidadAtencion objModAt = null;
            TipoModalidad objTipoMod = null;
            SqlConnection conexion = DB.Conectar();
            SqlCommand sqlcmd = null;
            try
            {
                string comando = "SELECT Especialidad.codEspecialidad, Especialidad.nombre, Especialidad.descripcion, Especialidad.fechaAlta, Especialidad.precioLista, "
                     + "Modalidad_Atencion.codModalidad AS 'CodModalidad', Modalidad_Atencion.duracionAtencion, Modalidad_Atencion.fechaVigenciaDesde, "
                     + "Tipo_Modalidad.codTipoModalidad AS 'CodTipoModalidad', Tipo_Modalidad.nombre AS 'NombreTipoModalidad' FROM Especialidad INNER JOIN "
                     + "Modalidad_Atencion ON Especialidad.modalidad = Modalidad_Atencion.codModalidad INNER JOIN "
                     + "Tipo_Modalidad ON Modalidad_Atencion.idTipoModalidad = Tipo_Modalidad.codTipoModalidad "
                     + "WHERE Especialidad.fechaBaja IS NULL  and (Modalidad_Atencion.fechaVigenciaHasta > GETDATE() or Modalidad_Atencion.fechaVigenciaHasta is null)";
                if (nombreEsp != null && fechaDesde != null)
                {
                    comando += "AND Especialidad.nombre LIKE '%" + nombreEsp + "%' AND Especialidad.fechaAlta >= @fechaAlta";
                    sqlcmd = new SqlCommand(comando, conexion);
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaAlta", fechaDesde));
                }
                else if (nombreEsp != null)
                {
                    comando += "AND Especialidad.nombre LIKE '%" + nombreEsp + "%'";
                    sqlcmd = new SqlCommand(comando, conexion);
                }
                else if (fechaDesde != null)
                {
                    comando += "AND Especialidad.fechaAlta >= @fechaAlta";
                    sqlcmd = new SqlCommand(comando, conexion);
                    sqlcmd.Parameters.Add(new SqlParameter("@fechaAlta", fechaDesde));
                }
                else
                    sqlcmd = new SqlCommand(comando, conexion);

                SqlDataReader dr = sqlcmd.ExecuteReader();
                list = new List<Especialidad>();
                while (dr.Read())
                {
                    //solo mostramos las especialidades "vigentes"
                    //o sea, las que NO tienen fecha de baja dada, NI modalidad de atencion vigente

                    objEsp = new Especialidad();
                    objEsp.CodEspecialidad = (int)dr["codEspecialidad"];
                    objEsp.Nombre = dr["nombre"].ToString();
                    if (dr["descripcion"] != DBNull.Value)
                        objEsp.Descripcion = dr["descripcion"].ToString();
                    objEsp.FechaAlta = Convert.ToDateTime(dr["fechaAlta"]);

                    objEsp.PrecioLista = float.Parse(dr["precioLista"].ToString());
                    objEsp.CodModalidad = (int)dr["CodModalidad"];

                    objModAt = new ModalidadAtencion();
                    objModAt.CodModalidad = (int)dr["CodModalidad"];
                    objModAt.DuracionAtencion = (int)dr["duracionAtencion"];
                    objModAt.FechaVigenciaDesde = Convert.ToDateTime(dr["fechaVigenciaDesde"]);
                    objModAt.CodTipoModalidad = (int)dr["CodTipoModalidad"];

                    objTipoMod = new TipoModalidad();
                    objTipoMod.CodTipoModalidad = (int)dr["CodTipoModalidad"];
                    objTipoMod.Nombre = dr["NombreTipoModalidad"].ToString();

                    objModAt.TipoModalidad = objTipoMod;
                    objEsp.ModalidadEspecialidad = objModAt;

                    list.Add(objEsp);
                    objEsp = null;
                }

            }
            catch (Exception)
            {

                throw;
            }
            return list;
        }

        public static ModalidadAtencion ObtenerModalidadAtencionEspecialidad(int codEsp)
        {
            ModalidadAtencion modEsp = null;
            SqlConnection conexion = DB.Conectar();

            try
            {
                string comando = "SELECT Especialidad.codEspecialidad, Especialidad.modalidad, Modalidad_Atencion.duracionAtencion, Modalidad_Atencion.fechaVigenciaDesde, Modalidad_Atencion.fechaVigenciaHasta, "
                               + "Modalidad_Atencion.idTipoModalidad, Tipo_Modalidad.nombre AS tipoModalidad FROM Especialidad INNER JOIN Modalidad_Atencion ON Especialidad.modalidad = Modalidad_Atencion.codModalidad "
                               + "INNER JOIN Tipo_Modalidad ON Modalidad_Atencion.idTipoModalidad = Tipo_Modalidad.codTipoModalidad "
                               + "WHERE (Especialidad.codEspecialidad = @codEsp) AND (Modalidad_Atencion.fechaVigenciaHasta IS NULL OR Modalidad_Atencion.fechaVigenciaHasta > GETDATE())";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@codEsp", codEsp));
                SqlDataReader dr = sqlcmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        modEsp = new ModalidadAtencion();
                        modEsp.CodModalidad = int.Parse(dr["modalidad"].ToString());
                        modEsp.DuracionAtencion = int.Parse(dr["duracionAtencion"].ToString());
                        modEsp.FechaVigenciaDesde = DateTime.Parse(dr["fechaVigenciaDesde"].ToString());
                        if (dr["fechaVigenciaHasta"] == DBNull.Value)
                            modEsp.FechaVigenciaHasta = null;
                        else
                            modEsp.FechaVigenciaHasta = DateTime.Parse(dr["fechaVigenciaHasta"].ToString());
                        modEsp.CodTipoModalidad = int.Parse(dr["idTipoModalidad"].ToString());
                        modEsp.TipoModalidad = new TipoModalidad();
                        modEsp.TipoModalidad.Nombre = dr["tipoModalidad"].ToString();

                    }
                }

            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return modEsp;
        }

        public static List<Medico> ObtenerMedicosDeEspecialidad(int idEsp)
        {
            List<Medico> listaMedicos = null;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT MedicoXEspecialidad.legajo, MedicoXEspecialidad.codEspecialidad, Persona.nombre, Persona.apellido "
                               + "FROM MedicoXEspecialidad INNER JOIN Medico ON MedicoXEspecialidad.legajo = Medico.legajo INNER JOIN Persona ON Medico.codPersona = Persona.codPersona "
                               + "WHERE (MedicoXEspecialidad.fechaBaja IS NULL OR MedicoXEspecialidad.fechaBaja > GETDATE()) AND (MedicoXEspecialidad.codEspecialidad = @codEsp)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@codEsp", idEsp));
                SqlDataReader dr = sqlcmd.ExecuteReader();

                if (dr.HasRows)
                {
                    listaMedicos = new List<Medico>();
                    Medico objMedico = null;
                    while (dr.Read())
                    {
                        objMedico = new Medico();
                        objMedico.Legajo = long.Parse(dr["legajo"].ToString());
                        objMedico.ApellidoNombre = dr["apellido"].ToString() + ", " + dr["nombre"].ToString();

                        listaMedicos.Add(objMedico);
                        objMedico = null;
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return listaMedicos;
        }

        public static float ObtenerPrecioListaEspecialidad(int idEsp)
        {
            float precio = 0;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT codEspecialidad, nombre, precioLista FROM Especialidad WHERE (codEspecialidad = @idEsp)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@idEsp", idEsp));
                SqlDataReader dr = sqlcmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        precio = float.Parse(dr["precioLista"].ToString());
                    }

                }
            }
            catch (Exception ex)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return precio;
        }

        public static float ObtenerPrecioMedicoEspecialidad(int idEsp)
        {
            float precio = 0;
            SqlConnection conexion = DB.Conectar();
            try
            {
                string comando = "SELECT codEspecialidad, nombre, precioMedico FROM Especialidad WHERE (codEspecialidad = @idEsp)";
                SqlCommand sqlcmd = new SqlCommand(comando, conexion);
                sqlcmd.Parameters.Add(new SqlParameter("@idEsp", idEsp));
                SqlDataReader dr = sqlcmd.ExecuteReader();
                if (dr.Read())
                {
                    precio = float.Parse(dr["precioMedico"].ToString());
                }
            }
            catch (Exception ex)
            {

                throw;
            }
            finally
            {
                conexion.Close();
            }
            return precio;
        }
        public static List<EspecialidadMedico> obtenerEspecXMedicoXDia(int dia)
        {
            SqlConnection con = DB.Conectar();
            SqlTransaction tran = con.BeginTransaction();
            List<EspecialidadMedico> leM = new List<EspecialidadMedico>();
            EspecialidadMedico eM = null;
            try
            {
                string comando = "select P.nombre as NombreMedico,P.apellido,M.legajo,M.idUsuario,Me.nroMatriculaEspecialidad,E.nombre as Especialidad,E.codEspecialidad,Me.tieneHorario,HA.horaDesde,HA.horaHasta,HA.diaSemana,Ma.idTipoModalidad "
                                    +"from MedicoXEspecialidad Me, Medico M, Persona P,Especialidad E,HorarioAtencionXMedicoXEspecialidad HA,Modalidad_Atencion Ma "
                                    +"where HA.diaSemana=@dia and Ma.codModalidad=E.modalidad and Ma.idTipoModalidad=2 and Me.tieneHorario=1 and Me.codEspecialidad=E.codEspecialidad and HA.codEspecialidad=Me.codEspecialidad and HA.legajo=Me.legajo and M.legajo=Me.legajo and M.codPersona=P.codPersona and Ha.fechaVigenciaHasta is null or Ha.fechaVigenciaHasta>GETDATE()";
                List<SqlParameter> lsp = new List<SqlParameter>();
                lsp.Add(new SqlParameter("@dia", dia));
                SqlDataReader dr = DB.GenerarReader(comando, lsp, con, tran);

                while (dr.Read())
                {
                    eM = new EspecialidadMedico();
                    eM.ApellidoNombreMedico = dr["NombreMedico"].ToString() + ", " + dr["apellido"].ToString();
                    eM.LegajoMedico = (long)dr["legajo"];
                    eM.NumeroMatriculaEspecialidad = (long)dr["nroMatriculaEspecialidad"];
                    eM.Especialidad = new Especialidad();
                    eM.Especialidad.Nombre = dr["Especialidad"].ToString();
                    eM.NombreEspecialidad = dr["Especialidad"].ToString();
                    eM.CodEspecialidad = (int)dr["codEspecialidad"];
                    eM.horarioAtencion = new HorarioAtencion();
                    eM.horarioAtencion.HoraDesde = (TimeSpan)dr["horaDesde"];
                    leM.Add(eM);
                    eM = null;
                }




            }
            catch (Exception)
            {

                throw;
            }
            finally { con.Close(); }
            return leM;
        }

    }
}
